package com.cdc.dbdemo;

import android.app.Dialog;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Toast;

import com.cdc.imoocdaily.CostBean;
import com.cdc.imoocdaily.R;

import java.util.ArrayList;
import java.util.List;

public class SqliteDemoActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite_demo);

    }

    /**
     *
     * 默认情况下都是创建或打开可读可写的数据库
     * 在磁盘文件已满或者是数据库本身有权限限制的情况下,getReadableDatabase打开的是只读的数据库
     * getWritableDatabase
     *
     *
     * getReadableDatabase
     *
     * @param view
     */
    public void createDb(View view) {
        DBManager.getHelper(this).getWritableDatabase();
    }
    /**
     * 插入数据
     * **/
    public void insertDb(View view) {
        String sql="insert into "+DBConstants.TABLE_NAME+" values ( 1,'zhangsan',20)";
        SQLiteDatabase db=DBManager.getHelper(this).getWritableDatabase();
        DBManager.execSQL(db,sql);
        String sql2="insert into "+DBConstants.TABLE_NAME+" values ( 2,'李四',30)";

        String sql3="insert into "+DBConstants.TABLE_NAME+" values ( 3,'王五',40)";
        DBManager.execSQL(db,sql2);
        DBManager.execSQL(db,sql3);
        db.close();
    }
    /**
     * 修改数据
     * **/
    public void updateDb(View view) {
        String sql="update  "+DBConstants.TABLE_NAME+"  set "+DBConstants.NAME+" ='张三' where "+DBConstants._id+"=1";
        SQLiteDatabase db=DBManager.getHelper(this).getWritableDatabase();
        DBManager.execSQL(db,sql);
        db.close();
    }

    /**
     * 删除数据
     * **/
    public void deleteDb(View view) {
        String sql="delete from  "+DBConstants.TABLE_NAME+" where "+DBConstants._id+"=1";
        SQLiteDatabase db=DBManager.getHelper(this).getWritableDatabase();
        DBManager.execSQL(db,sql);
        db.close();
    }

    /**
     * 插入数据api
     * String table,数据表名
     * String nullColumnHack,不许出现全部列为空的情况，一般指定此字段为null
     * ContentValues values  键为String类型的HashMap集合
     * 返回long  表示插入数据的列数
     * @param view
     */
    public void insertApiDb(View view) {
        SQLiteDatabase db=DBManager.getHelper(this).getWritableDatabase();
        ContentValues contentValues=new ContentValues();
        //数据库字段名  数据库字段值
        contentValues.put(DBConstants._id,5);
        contentValues.put(DBConstants.NAME,"张飞");
        contentValues.put(DBConstants.AGE,50);
        long result=db.insert(DBConstants.TABLE_NAME,null,contentValues);
        if(result>0){
            Toast.makeText(this,"插入数据成功",Toast.LENGTH_LONG).show();
        }else{
            Toast.makeText(this,"插入数据失败",Toast.LENGTH_LONG).show();
        }
        db.close();
    }

    /**
     * String table, 数据表名
     * ContentValues values,键为String类型的HashMap集合
     * String whereClause, 当前修改的条件
     * String[] whereArgs  修改条件的占位符
     * 返回 int 表示修改的条数
     * @param view
     */
    public void updateApiDb(View view) {
        SQLiteDatabase db=DBManager.getHelper(this).getWritableDatabase();
        ContentValues contentValues=new ContentValues();
        //要修改的数据库字段名  要修改的数据库字段值
        contentValues.put(DBConstants.NAME,"关羽");
        //第一种写法
       //int result=db.update(DBConstants.TABLE_NAME,contentValues,DBConstants._id+"=5",null);
        //第二种写法
        int result=db.update(DBConstants.TABLE_NAME,contentValues,DBConstants._id+"=?",new String[]{"5"});
        if(result>0){
            Toast.makeText(this,"修改数据成功",Toast.LENGTH_LONG).show();
        }else{
            Toast.makeText(this,"修改数据失败",Toast.LENGTH_LONG).show();
        }
        db.close();


    }

    /**
     *String table, 数据表名
     * String whereClause, 删除条件
     * String[] whereArgs  删除占位符
     * @param view
     */
    public void deleteApiDb(View view) {
        SQLiteDatabase db=DBManager.getHelper(this).getWritableDatabase();

        int result=db.delete(DBConstants.TABLE_NAME,DBConstants._id+"=?",new String[]{"2"});
        if(result>0){
            Toast.makeText(this,"删除数据成功",Toast.LENGTH_LONG).show();
        }else{
            Toast.makeText(this,"删除数据失败",Toast.LENGTH_LONG).show();
        }
        db.close();
    }

    /**
     * 查询数据 api
     * @param view
     */
    public List<Person> quaryApiDb(View view) {
        List<Person> lists=new ArrayList<>();
        SQLiteDatabase db=DBManager.getHelper(this).getWritableDatabase();
        Cursor cursor=db.query(DBConstants.TABLE_NAME,null,null,null,null,null,null);
        if (cursor == null) {
            return null;
        }
        while (cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex(DBConstants._id));
            String name=cursor.getString(cursor.getColumnIndex(DBConstants.NAME));
            int age= cursor.getInt(cursor.getColumnIndex(DBConstants.AGE));
            Person p=new Person(id,name,age);
            Log.e("cdc",p.toString());
            lists.add(p);
        }
        if(lists.size()>0){
            Toast.makeText(this,"查询数据成功:"+lists.size(),Toast.LENGTH_LONG).show();
        }
        cursor.close();
        db.close();
        return lists;
    }

    public void quarySqlDb(View view) {
        SQLiteDatabase db=DBManager.getHelper(this).getWritableDatabase();
        String sql="select * from "+DBConstants.TABLE_NAME;
        Cursor cursor=DBManager.selectBySql(db,sql,null);
        List<Person>  lists=DBManager.cursorToList(cursor);
        if(lists.size()>0){
            Toast.makeText(this,"查询数据成功:"+lists.size(),Toast.LENGTH_LONG).show();
        }
        db.close();



    }
}
